package com.geline.sqlbackup.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.HexUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.DbType;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.geline.sqlbackup.bean.ColumnInfo;
import com.geline.sqlbackup.bean.TableColumnInfo;
import lombok.extern.slf4j.Slf4j;

import java.io.Serializable;
import java.util.*;
import java.util.stream.Collectors;

/**
 * sql解析工具类
 * @author: mengx
 * @date: 2021/8/14 15:12
 */
@Slf4j
public class SqlParseUtils {

    public static final String COLUMN_CREATE_TIME = "create_time";//创建时间
    public static final String COLUMN_UPDATE_TIME = "update_time";//更新时间
    public static final String COLUMN_CREATE_BY = "create_by";//创建人名
    public static final String COLUMN_UPDATE_BY = "update_by";//更新人名
    public static final String COLUMN_IS_DELETE = "is_deleted";//逻辑删除 0否1是

    /**
     * 特殊类型格式化返回
     * @param tableColumnInfo
     * @param columnName
     * @param value
     * @return
     */
    public static String getValueString(TableColumnInfo tableColumnInfo, String columnName, Object value){
        List<ColumnInfo> columnInfoList = tableColumnInfo.getColumnInfoList();
        if(value instanceof Date){
            for(ColumnInfo columnInfo : columnInfoList){
                if(columnInfo.getColumnName().equals(columnName)){
                    //"year", "date", "datetime", "timestamp"
                    String columnType = columnInfo.getColumnType().toLowerCase();
                    if("datetime".equalsIgnoreCase(columnType.trim())){
                        String valueStr = DateUtil.formatDateTime((Date) value);
                        return StringUtils.quotaMark(valueStr);
                    }else if(columnType.trim().startsWith("timestamp")){
                        //timestamp(3)
                        return StringUtils.quotaMark(value.toString());
                    }else if("date".equalsIgnoreCase(columnType.trim())){
                        String valueStr = DateUtil.formatDate((Date) value);
                        return StringUtils.quotaMark(valueStr);
                    }
                }
            }
        }else if(value instanceof ArrayList){//举例：ancestors
            for(ColumnInfo columnInfo : columnInfoList){
                if(columnInfo.getColumnName().equals(columnName)){
                    if(columnInfo.isStringFlag()){
                        //祖级列表字段，传入为List转为 String
                        List list = (ArrayList)value;

                        if(list.size()>0){
                            return StringUtils.quotaMark(StrUtil.join(",", list));
                        }else {
                            return StringUtils.quotaMark("");
                        }
                    }
                }
            }
        }else if(value instanceof Byte[] || value instanceof byte[]){
            String hexStr = "0x"+HexUtil.encodeHexStr((byte[]) value);
            return hexStr;
        }
        return StringUtils.quotaMark(value);
    }

    public static String createInsertOneSql(boolean showColumns, TableColumnInfo tableColumnInfo, Map<String, Object> valueMap, String schemaName){
        List<ColumnInfo> columnInfoList = tableColumnInfo.getColumnInfoList();
        List<String> colNameList = columnInfoList.stream().map(row -> row.getColumnName()).collect(Collectors.toList());
        return createInsertOneSql(showColumns, tableColumnInfo, colNameList, valueMap, schemaName);
    }

    /**
     * 根据查询SQL构造插入SQL；不处理null值
     * @param showColumns 显示插入字段 true是，false否
     * @param tableColumnInfo
     * @param colNameList
     * @param valueMap
     * @return
     */
    public static String createInsertOneSql(boolean showColumns, TableColumnInfo tableColumnInfo, List<String> colNameList, Map<String, Object> valueMap, String schemaName){
        List<String> valueList = new ArrayList<>();
        for(String columnName : colNameList){
            //key = column_name
            Object value1 = valueMap.get(columnName);
            //key = columnName
            Object value2 = valueMap.get(StrUtil.toCamelCase(columnName));
            //key = COLUMN_NAME
            Object value3 = valueMap.get(columnName.toUpperCase());
            Object value = value1!=null ? value1 : (value2!=null ? value2 : (value3!=null ? value3 : null));
            //key = column_name
            Object value4 = valueMap.get(columnName.toLowerCase());
            value = value == null ? value4 : value;
            if(value != null){
                String valueStr = getValueString(tableColumnInfo, columnName, value);
                valueList.add(valueStr);
            }else {
                valueList.add(null);
            }
        }
        String tableName = tableColumnInfo.getTableName();
        if(showColumns){
            String columns = CollUtil.join(colNameList, ",");
            //"INSERT INTO ${table} (${columns}) VALUES (${values})"
            String formatSql = "INSERT INTO "+schemaName+".%s (%s) VALUES (%s)";

            // mysql不加用户名
            if (StrUtil.isBlank(schemaName)) {
                formatSql = "INSERT INTO %s (%s) VALUES (%s)";
            }

            String values = CollUtil.join(valueList, ",");
            return String.format(formatSql, tableName, columns, values);
        }else {
            //"INSERT INTO ${table} (${columns}) VALUES (${values})"
            String formatSql = "INSERT INTO "+schemaName+".%s VALUES (%s)";

            // mysql不加用户名
            if (StrUtil.isBlank(schemaName)) {
                formatSql = "INSERT INTO %s VALUES (%s)";
            }
            String values = CollUtil.join(valueList, ",");
            return String.format(formatSql, tableName, values);
        }
    }

    /**
     * 处理默认值
     * @param tableColumnInfo
     * @param valueMap
     */
    public static void handleInsertColumn(TableColumnInfo tableColumnInfo, Map<String, Object> valueMap){
        //处理主键
        ColumnInfo primaryKey = tableColumnInfo.getPrimaryKey();
        String key = primaryKey.getColumnName();
        Object value = valueMap.get(key);
        if(value == null){
            //String javaFieldType = primaryKey.getJavaFieldType();//javaFieldType.contains("String")
            boolean stringFlag = primaryKey.isStringFlag();
            if(stringFlag){
                valueMap.put(key, IdUtil.fastSimpleUUID());
            }else {
                log.debug("auto primaryKey = {}, stringFlag = {}", key, stringFlag);
            }
        }
        //处理：create_time, update_time, create_by, update_by
        List<ColumnInfo> columnInfoList = tableColumnInfo.getColumnInfoList();
        for (ColumnInfo columnInfo : columnInfoList){
            String columnName = columnInfo.getColumnName();
            if(COLUMN_CREATE_TIME.equals(columnName) && valueMap.get(COLUMN_CREATE_TIME)==null){
                valueMap.put(COLUMN_CREATE_TIME, DateUtil.formatDateTime(new Date()));
            }else if(COLUMN_UPDATE_TIME.equals(columnName) && valueMap.get(COLUMN_UPDATE_TIME)==null){
                valueMap.put(COLUMN_UPDATE_TIME, DateUtil.formatDateTime(new Date()));
            }else if(COLUMN_CREATE_BY.equals(columnName) && valueMap.get(COLUMN_CREATE_BY)==null){
                //valueMap.put(COLUMN_CREATE_BY, SaTokenUtil.getUserName());
            }else if(COLUMN_UPDATE_BY.equals(columnName) && valueMap.get(COLUMN_UPDATE_BY)==null){
                //valueMap.put(COLUMN_UPDATE_BY, SaTokenUtil.getUserName());
            }
        }
    }

    public static void handleUpdateColumn(TableColumnInfo tableColumnInfo, Map<String, Object> valueMap){
        //处理日期：update_time, update_by
        List<ColumnInfo> columnInfoList = tableColumnInfo.getColumnInfoList();
        for (ColumnInfo columnInfo : columnInfoList){
            String columnName = columnInfo.getColumnName();
            if(COLUMN_UPDATE_TIME.equals(columnName) && valueMap.get(COLUMN_UPDATE_TIME)==null){
                valueMap.put(COLUMN_UPDATE_TIME, DateUtil.formatDateTime(new Date()));
            }else if(COLUMN_UPDATE_BY.equals(columnName) && valueMap.get(COLUMN_UPDATE_BY)==null){
                //valueMap.put(COLUMN_UPDATE_BY, SaTokenUtil.getUserName());
            }
        }
    }

    public static String createInsertNotNullSql(TableColumnInfo tableColumnInfo, Map<String, Object> valueMap){
        List<ColumnInfo> columnInfoList = tableColumnInfo.getColumnInfoList();
        List<String> colNameList = columnInfoList.stream().map(row -> row.getColumnName()).collect(Collectors.toList());
        return createInsertNotNullSql(tableColumnInfo, colNameList, valueMap);
    }

    /**
     * 根据传入参数构造插入SQL；不处理null值
     * @param colNameList
     * @param valueMap
     * @return
     */
    public static String createInsertNotNullSql(TableColumnInfo tableColumnInfo, List<String> colNameList, Map<String, Object> valueMap){
        //处理默认字段 id,create_time,update_time, create_by, update_by
        handleInsertColumn(tableColumnInfo, valueMap);

        List<String> newColNameList = new ArrayList<>();
        List<Object> valueList = new ArrayList<>();
        for(String columnName : colNameList){
            //key = column_name
            Object value1 = valueMap.get(columnName);
            if(value1 != null && StrUtil.isNotBlank(String.valueOf(value1))){
                newColNameList.add(columnName);
                String valueStr = getValueString(tableColumnInfo, columnName, value1);
                valueList.add(valueStr);
                continue;
            }
            //key = columnName
            Object value2 = valueMap.get(StrUtil.toCamelCase(columnName));
            if(value2 != null && StrUtil.isNotBlank(String.valueOf(value2))){
                newColNameList.add(columnName);
                String valueStr = getValueString(tableColumnInfo, columnName, value2);
                valueList.add(valueStr);
                continue;
            }
            //key = COLUMN_NAME
            Object value3 = valueMap.get(columnName.toUpperCase(Locale.ENGLISH));
            if(value3 != null && StrUtil.isNotBlank(String.valueOf(value3))){
                newColNameList.add(columnName);
                String valueStr = getValueString(tableColumnInfo, columnName, value3);
                valueList.add(valueStr);
                continue;
            }
        }

        String sql = null;
        if(newColNameList.size()>0){
            String columns = CollUtil.join(newColNameList, ",");
            //"INSERT INTO ${table} (${columns}) VALUES (${values})"
            String formatSql = "INSERT INTO %s (%s) VALUES (%s)";
            String values = CollUtil.join(valueList, ",");
            String tableName = tableColumnInfo.getTableName();
            sql = String.format(formatSql, tableName, columns, values);
        }
        return sql;
    }

    public static String createSelectOneSqlBySql(DbType dbType, String sql, Serializable id){
        String tableName = SqlSelectUtils.parseTableName(dbType, sql);
        return createSelectOneSqlByTable(tableName, StringUtils.quotaMark(id));
    }

    /**
     * 根据sql构造查询一条记录sql
     * @param tableName
     * @param id
     * @return
     */
    public static String createSelectOneSqlByTable(String tableName, Serializable id){
        //SELECT * FROM ${table} WHERE id=#{id}
        String formatSql = "SELECT * FROM %s WHERE id=%s";
        return String.format(formatSql, tableName, StringUtils.quotaMark(id));
    }

    /**
     * 根据id更新，支持单个id,多个idList
     * @param tableColumnInfo
     * @param valueMap
     * @return
     */
    public static String createUpdateByIdSql(TableColumnInfo tableColumnInfo, Map<String, Object> valueMap){
        //处理默认字段
        ColumnInfo primaryKey = tableColumnInfo.getPrimaryKey();
        String primaryName = primaryKey.getColumnName();
        Object primaryValue = valueMap.get(primaryName);
        Objects.requireNonNull(primaryValue, "primary key("+primaryName +") cannot be empty!");

        List<ColumnInfo> columnInfoList = tableColumnInfo.getColumnInfoList();
        List<String> colNameList = columnInfoList.stream().map(row -> row.getColumnName()).collect(Collectors.toList());
        List<String> columnValueList = new ArrayList<>();
        for(String columnName : colNameList){
            if(!primaryName.equals(columnName)){
                //key = column_name
                Object value1 = valueMap.get(columnName);
                //key = columnName
                Object value2 = valueMap.get(StrUtil.toCamelCase(columnName));
                //key = COLUMN_NAME
                Object value3 = valueMap.get(columnName.toUpperCase(Locale.ENGLISH));
                Object value = value1!=null ? value1 : (value2!=null ? value2 : (value3!=null ? value3 : null));
                if(value != null){
                    String valueString = getValueString(tableColumnInfo, columnName, value);
                    columnValueList.add(columnName+"="+valueString);
                }
            }
        }
        if(primaryValue instanceof Collection){
            List<String> idList = (List)primaryValue;
            String ids = null;
            if(primaryKey.isStringFlag()){
                ids = "'"+StrUtil.join("','", idList)+"'";
            }else {
                ids = StrUtil.join(",", idList);
            }
            String formatSql = "UPDATE %s SET %s WHERE %s in (%s)";
            String columnValues = CollUtil.join(columnValueList, ",");
            String tableName = tableColumnInfo.getTableName();
            return String.format(formatSql, tableName, columnValues, primaryName, ids);
        }else {
            //Class primaryClass = ClassUtil.loadClass(primaryKey.getJavaFieldType());
            //String idValue = primaryClass==String.class ? "'"+primaryValue+"'" : (String) primaryValue;
            boolean stringFlag = primaryKey.isStringFlag();
            Object idValue = stringFlag ? "'"+primaryValue+"'" : primaryValue;
            String formatSql = "UPDATE %s SET %s WHERE %s = %s";
            String columnValues = CollUtil.join(columnValueList, ",");
            String tableName = tableColumnInfo.getTableName();
            return String.format(formatSql, tableName, columnValues, primaryName, idValue);
        }
    }
}
